﻿namespace DatabaseManage
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Linq;

    public class MSSql2000 : AbsDatabaseInfo
    {
        public MSSql2000()
        {
        }

        public MSSql2000(string connectionString)
        {
            base.ConnectionString = connectionString;
        }

        public override bool CheckDatabaseConnection()
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(base.ConnectionString))
                {
                    connection.Open();
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        public override DataTable GetDatabaseAllTableNames()
        {
            DataTable dataTable = null;
            try
            {
                using (SqlConnection connection = new SqlConnection(base.ConnectionString))
                {
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.CommandType = CommandType.Text;
                        command.CommandText = "select TableName=[Name] from sysobjects where type='U'";
                        SqlDataAdapter adapter = new SqlDataAdapter(command);
                        dataTable = new DataTable();
                        adapter.Fill(dataTable);
                    }
                    return dataTable;
                }
            }
            catch (Exception)
            {
                return null;
            }
            return dataTable;
        }

        public override DataTable GetDatabaseAllViewNames()
        {
            DataTable dataTable = null;
            try
            {
                using (SqlConnection connection = new SqlConnection(base.ConnectionString))
                {
                    using (SqlCommand command = connection.CreateCommand())
                    {
                        command.CommandType = CommandType.Text;
                        command.CommandText = "select ViewName=[Name] from sysobjects where type='V'";
                        SqlDataAdapter adapter = new SqlDataAdapter(command);
                        dataTable = new DataTable();
                        adapter.Fill(dataTable);
                    }
                    return dataTable;
                }
            }
            catch (Exception)
            {
                return null;
            }
            return dataTable;
        }

        public override List<FieldInfo> GetTableAllFieldInfos(string tableName)
        {
            List<FieldInfo> list = new List<FieldInfo>();
            string tablePrimaryKeyName = this.GetTablePrimaryKeyName(tableName);
            List<FieldCommentInfo> tableFieldComments = this.GetTableFieldComments(tableName);
            try
            {
                using (SqlConnection connection = new SqlConnection(base.ConnectionString))
                {
                    using (SqlCommand command = new SqlCommand("sp_columns", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add("@table_name", SqlDbType.NVarChar);
                        command.Parameters["@table_name"].Value = tableName;
                        connection.Open();
                        Func<FieldCommentInfo, bool> func = null;
                        using (SqlDataReader dr = command.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                FieldInfo item = new FieldInfo {
                                    FieldName = dr["COLUMN_NAME"].ToString(),
                                    FieldType = dr["TYPE_NAME"].ToString(),
                                    IsNull = (dr["IS_NULLABLE"].ToString() == "NO") ? false : true,
                                    IsPkey = (dr["COLUMN_NAME"].ToString().ToLower() == tablePrimaryKeyName.ToLower()) ? true : false,
                                    TypeLength = dr["LENGTH"].ToString()
                                };
                                if (func == null)
                                {
                                    func = c => c.FieldName.ToLower() == dr["COLUMN_NAME"].ToString().ToLower();
                                }
                                List<FieldCommentInfo> list3 = Enumerable.Where<FieldCommentInfo>(tableFieldComments, func).ToList<FieldCommentInfo>();
                                item.Comment = ((list3 != null) && (list3.Count > 0)) ? list3[0].Comment.Replace("\r", "").Replace("\n", "") : "";
                                list.Add(item);
                            }
                        }
                    }
                    return list;
                }
            }
            catch
            {
                return null;
            }
            return list;
        }

        public override string GetTableCommentInfo(string tableName)
        {
            string str = "";
            SqlConnection connection = new SqlConnection(base.ConnectionString);
            try
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand("SELECT dbo.sysproperties.[value] from dbo.sysproperties INNER JOIN dbo.sysobjects ON dbo.sysproperties.id = dbo.sysobjects.id WHERE (dbo.sysproperties.smallid = 0) AND (dbo.sysobjects.name='" + tableName + "')", connection))
                {
                    object obj2 = command.ExecuteScalar();
                    if (obj2 != null)
                    {
                        str = obj2.ToString();
                    }
                }
                return str;
            }
            catch
            {
                str = "";
            }
            finally
            {
                if (connection != null)
                {
                    connection.Dispose();
                }
            }
            return str;
        }

        private List<FieldCommentInfo> GetTableFieldComments(string tableName)
        {
            string cmdText = "SELECT p.[value],c.[name] from dbo.sysproperties p INNER JOIN dbo.sysobjects o ON o.id = p.id INNER JOIN dbo.syscolumns c ON p.id = c.id AND p.smallid = c.colid WHERE (o.name = '" + tableName + "')";
            List<FieldCommentInfo> list = new List<FieldCommentInfo>();
            try
            {
                using (SqlConnection connection = new SqlConnection(base.ConnectionString))
                {
                    using (SqlCommand command = new SqlCommand(cmdText, connection))
                    {
                        connection.Open();
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                FieldCommentInfo item = new FieldCommentInfo {
                                    FieldName = reader["name"].ToString(),
                                    Comment = reader["value"].ToString()
                                };
                                list.Add(item);
                            }
                        }
                    }
                    return list;
                }
            }
            catch
            {
                return null;
            }
            return list;
        }

        private string GetTablePrimaryKeyName(string tableName)
        {
            string str = "";
            try
            {
                using (SqlConnection connection = new SqlConnection(base.ConnectionString))
                {
                    using (SqlCommand command = new SqlCommand("sp_pkeys", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add("@table_name", SqlDbType.NVarChar);
                        command.Parameters["@table_name"].Value = tableName;
                        connection.Open();
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                str = reader["COLUMN_NAME"].ToString();
                            }
                        }
                    }
                    return str;
                }
            }
            catch
            {
                return "";
            }
            return str;
        }

        public override List<FieldInfo> GetViewAllFieldInfos(string viewName)
        {
            List<FieldInfo> list = new List<FieldInfo>();
            try
            {
                using (SqlConnection connection = new SqlConnection(base.ConnectionString))
                {
                    using (SqlCommand command = new SqlCommand("sp_columns", connection))
                    {
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Add("@table_name", SqlDbType.NVarChar);
                        command.Parameters["@table_name"].Value = viewName;
                        connection.Open();
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                FieldInfo item = new FieldInfo {
                                    FieldName = reader["COLUMN_NAME"].ToString(),
                                    FieldType = reader["TYPE_NAME"].ToString(),
                                    IsNull = (reader["IS_NULLABLE"].ToString() == "NO") ? false : true,
                                    IsPkey = false,
                                    TypeLength = reader["LENGTH"].ToString(),
                                    Comment = ""
                                };
                                list.Add(item);
                            }
                        }
                    }
                    return list;
                }
            }
            catch
            {
                return null;
            }
            return list;
        }

        public override void SetConnectionString(string dbAdd, string dbName, string userName, string pwd)
        {
            base.ConnectionString = "server=" + dbAdd + ";database=" + dbName + ";uid=" + userName + ";pwd=" + pwd;
        }
    }
}

